
create external table jms_tmp.tmp_cainiao_active_tn_history_dt (
  taking_code string COMMENT 揽收网点,
  taking_name string COMMENT ,
  dispatch_code string COMMENT 派件网点,
  dispatch_name string COMMENT ,
  start_hm string COMMENT 开始时段,
  end_hm string COMMENT 开始时段,
  line_str string COMMENT 线路code,
  aging_days int COMMENT 时效天数,
  line_cnt int COMMENT (始末网点,揽收时段,线路,时效,线路)单量,
  aging_days_cnt int COMMENT (始末网点,揽收时段,时效)单量,
  taking_hm_cnt int COMMENT (始末网点,揽收时段)单量,
  se_network_total int COMMENT 始末网点单量,
  bill_total bigint COMMENT 基数总单量,
  sort_number int COMMENT 排序,
  update_time timestamp COMMENT 更新时间,
  line_str_name string COMMENT 线路名称,
  is_within_shift tinyint COMMENT 1班次内0班次外
) comment 菜鸟动态时效_历史线路单量
partitioned by (dt string comment 时间分区)
stored as parquet
location /dw/hive/jms_tmp.db/external/tmp_cainiao_active_tn_history_dt
tblproperties (
discover.partitions=false,
parquet.column.index.access=true
);



create external table jms_tmp.tmp_cainiao_active_tn_match_dt (
  taking_code string COMMENT 揽收网点code,
  taking_name string COMMENT 揽收网点name,
  deliver_code string COMMENT 派件网点code,
  deliver_name string COMMENT 派件网点name,
  start_hm string COMMENT 开始时段,
  end_hm string COMMENT 开始时段,
  line_str string COMMENT 线路code,
  aging_days int COMMENT 时效天数,
  out_sign_days int COMMENT 静态时效天,
  is_within_shift tinyint COMMENT 是否班次内 1是0否,
  detail_route_id string COMMENT 静态路由id明细,
  center_line_s string COMMENT 中心线路_静态,
  is_main_route int COMMENT 是否主用路由 1 3主用0备用,
  route_id string COMMENT 路由 id ,
  weight int COMMENT 线路排序权重,
  line_rn string COMMENT 线路排序
) COMMENT 动态和静态匹配中间表
PARTITIONED BY (dt STRING COMMENT 日期分区)
STORED AS PARQUET
LOCATION /dw/hive/jms_tmp.db/external/tmp_cainiao_active_tn_match_dt
TBLPROPERTIES (
discover.partitions=false,
parquet.column.index.access=true
);


CREATE EXTERNAL TABLE jms_dm.dm_cainiao_active_tn_v2_dt(
  taking_code string COMMENT 最早揽收网点,
  taking_name string COMMENT 最早揽收网点name,
  deliver_code string COMMENT 第一次派件网点,
  deliver_name string COMMENT 第一次派件网点,
  start_hm string COMMENT 揽收时分开始,
  end_hm string COMMENT 揽收时分结束 ,
  days string COMMENT 取的标准时效,
  date_time string COMMENT 查询日期,
  rt decimal(16,4) COMMENT 占比,
  route_id string COMMENT route_id,
  is_bushu tinyint COMMENT 1时段补,2网点无发货补,
  detail_route_id string COMMENT 路由明细id)
COMMENT 菜鸟动态时效
PARTITIONED BY (
  dt string COMMENT 日期 分区时间)
LOCATION /dw/hive/jms_dm.db/external/dm_cainiao_active_tn_v2_dt
TBLPROPERTIES (
discover.partitions=false,
parquet.column.index.access=true
);

alter table jms_tmp.tmp_cainiao_active_tn_history_dt add columns (in_shift_num string comment'散货班次')cascade;
alter table jms_tmp.tmp_cainiao_active_tn_history_dt add columns (line_shift_cnt int comment'线路散货班次货量')cascade;
alter table jms_tmp.tmp_cainiao_active_tn_match_dt add columns (in_shift_num string comment'散货班次')cascade;
alter table jms_tmp.tmp_cainiao_active_tn_match_dt add columns (shift_weight string comment'班次权重')cascade;
alter table jms_tmp.tmp_cainiao_active_tn_match_dt change column weight line_weight int COMMENT'线路排序权重' cascade;
alter table jms_tmp.tmp_cainiao_active_tn_match_dt add columns (out_last_shift_code string comment'动态散货班次')cascade;
